import sqlite3


class Sqlite():

    database = 'data.db'
    __conn = None

    def __init__(self, dbname=None) -> None:
        dbname = dbname or Sqlite.database
        if not self.__class__.__conn:
            self.__class__.__conn = sqlite3.connect(dbname)

    def __select(self, sql, param=()):
        """ 查询 """
        cursor = self.__conn.cursor()
        cursor.execute(sql, param)
        result = cursor.fetchall()
        column = cursor.description
        cursor.close()
        return result, column

    def select(self, sql, param=()):
        """ 查询 """
        return self.__select(sql, param)[0]

    def execute(self, sql, param=()):
        """ 执行 """
        cursor = self.__conn.cursor()
        cursor.execute(sql, param)
        self.__conn.commit()
        cursor.close()

    def select_to_dict(self, sql, param=()) -> list:
        """ 将查询结果和列名组成字典 """
        result, column = self.__select(sql, param)
        keys = [tuple[0] for tuple in column]
        return [dict(zip(keys, row)) for row in result]


if __name__ == '__main__':
    sqlite = Sqlite()
    sqlite.execute('insert into user(id,name,age) values (5,"张三",10)')
    sqlite.execute('insert into user(id,name,age) values (6,"李四",12)')
    sqlite.execute('insert into user(id,name,age) values (7,"王五",14)')
    sqlite.execute('insert into user(id,name,age) values (8,"赵六",15)')
    print(sqlite.select('select * from user'))
    print(sqlite.select_to_dict('select * from user'))
